﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public partial class BienBanChotKL : System.Web.UI.Page
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBienBanChotKL";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double sochuyen = 0,klxebt_kltx=0, klxeben_klmua = 0, kltram_kltx = 0, klnvkd_kltx = 0, klnvkd_klban = 0,klnvkd_ttban=0, klnvkd_klnvkd = 0, klxebom_kltx = 0,phiruaxe=0,thanhtien_xebom=0;
    static string ncc = "";
    static System.Data.DataTable dt;
    static System.Data.DataTable dtPrint;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
            }


            CurrentPage = 1;
            Title = "Báo cáo chốt khối lượng | " + Header;

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            
            LoadBSXByTime(dlBienSo);
            LoadNVKD(dlNhanVien);
            LoadKH(dlKhachHang);
            LoadMacTX(dlMacTX);
            LoadloaiDa(dlLoaiDa);

            dlNhanVien.Visible = false;
            dlBienSo.Visible = true;
            dlLoaiBaoCao.SelectedIndex = 0;
        }
    }
    void PrintAll()
    {
        var workbook = new HSSFWorkbook();
        string ngaythang = "";
        
            string sheetname = "BB chốt KL Xe ben";
            
            var sheet = workbook.CreateSheet(sheetname);

            #region CSS

            

            //--------------------------

            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------
            var NoneStyle = workbook.CreateCellStyle();

            NoneStyle.SetFont(headerLabelFont);
        ////////////
            var hs2center = workbook.CreateCellStyle();
            hs2center.Alignment = HorizontalAlignment.CENTER;

            var hsb21 = workbook.CreateFont();
            hsb21.FontName = "Times New Roman";

            hsb21.FontHeightInPoints = 11;
            hsb21.Color = HSSFColor.BLACK.index;
            hs2center.SetFont(hsb21);

        /////////////////////////

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion
            
            //Xe ben----------------------------------------
            //Start header-----
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            ngaythang = "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BIÊN BẢN CHỐT KHỐI LƯỢNG XE BEN " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            
            cra = new CellRangeAddress(2, 2, 0, 6);
            sheet.AddMergedRegion(cra);

            //Ngày tháng
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Lâm Thao, Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm " + DateTime.Now.Year.ToString());
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 6);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header_xeben = { "Ngày nhập", "Biển số", "Nhà cung cấp", "Nơi nhận", "Loại vật liệu", "KL mua" };
            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header_xeben.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header_xeben[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }

            #endregion
            //End header-------
            //ghi du lieu tung dong
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@Index", dlBienSo.SelectedIndex > 0 ? 1 : 2);
            p[3].SqlDbType = SqlDbType.Int;

            var data = ex.GetData("sp_BienBanChotKL_XeBen_Print", p);

            #region Rows
            for (int i = 0; i < data.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j < data.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 6)
                    {
                        if (!data.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(data.Rows[i][j].ToString()));
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(data.Rows[i][j].ToString());
                            cell.CellStyle = headerLabelCellStyle;
                        }
                    }
                    else
                    {
                        cell.SetCellValue(data.Rows[i][j].ToString());
                        cell.CellStyle = headerLabelCellStyle;
                    }

                    cell.Row.Height = 500;
                }

            }
            #endregion

            //Footer
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            
            cell = row.CreateCell(3);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            cell = row.CreateCell(6);
            cell.CellFormula = "SUM(G7:G" + rowIndex.ToString() + ")";
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            //fix border
            for (int i1 = 0; i1 < data.Columns.Count; i1++)
            {
                if (i1 != 3 && i1 != 6)
                {
                    r1c1 = row.CreateCell(i1);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }
            
            //End footer
            for (int ik = 0; ik <= data.Columns.Count; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }

            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(0);
            cell.SetCellValue("Lái xe kí nhận");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            //truong bp
            cell = row.CreateCell(2);
            cell.SetCellValue("Trưởng BP điều hành xe");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            //ke toan
            cell = row.CreateCell(4);
            cell.SetCellValue("Kế toán");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            //truong hcth
            cell = row.CreateCell(5);
            cell.SetCellValue("Trưởng BP điều hành xe");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            //End Xe ben--------------------------------------------------------------------


            sheet = workbook.CreateSheet("BB chốt KL trạm trộn + TN");
            //Start Tram tron
            #region Header
            //tao hnag dau tien
            rowIndex = 0;

            //cong ty
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            ngaythang = "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BIÊN BẢN CHỐT KHỐI LƯỢNG TRẠM TRỘN + TN " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 7);
            sheet.AddMergedRegion(cra);

            //Ngày tháng
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Lâm Thao, Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm " + DateTime.Now.Year.ToString());
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 7);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            
            string[] header_tramtron = { "Ngày xuất", "Tên khách hàng", "Mác hợp đồng", "Mác thực xuất", "KL thực xuất", "Loại đá", "Loại phụ gia" };

            cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header_tramtron.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header_tramtron[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }

            #endregion
            //End header-----------
            //ghi du lieu tung dong

            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
            p[1].SqlDbType = SqlDbType.SmallDateTime;

            data = ex.GetData("sp_BienBanChotKL_TramTron_Print", p);

            for (int i = 0; i < data.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j < data.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 5)
                    {
                        if (!data.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(data.Rows[i][j].ToString()));
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(data.Rows[i][j].ToString());
                            cell.CellStyle = headerLabelCellStyle;
                        }
                    }
                    else
                    {
                        cell.SetCellValue(data.Rows[i][j].ToString());
                        if (j == 3 || j == 4)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }
            #region Footer
            //Footer
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(2);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            cell = row.CreateCell(5);
            cell.CellFormula = "SUM(F7:F" + rowIndex.ToString() + ")";
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            //fix border
            for (int i1 = 0; i1 < data.Columns.Count; i1++)
            {
                if (i1 != 2 && i1 != 5)
                {
                    r1c1 = row.CreateCell(i1);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }

            //End footer
            for (int ik = 0; ik <= data.Columns.Count; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }
            #endregion

            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(0);
            cell.SetCellValue("Bộ phận thí nghiệm");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            //truong bp
            cell = row.CreateCell(2);
            cell.SetCellValue("Bộ phận vận hành trạm");
            cell.Row.Height = 500;
            cell.CellStyle = hs2center;

            //ke toan
            cell = row.CreateCell(5);
            cell.SetCellValue("Kế toán trạm trộn");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            
            //end tram tron


            sheet = workbook.CreateSheet("BB chốt KL NVKD");
            //start NVKD
            #region Header
            //tao hnag dau tien
            rowIndex = 0;

            //cong ty
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            ngaythang = "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BIÊN BẢN CHỐT KHỐI LƯỢNG NVKD " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 10);
            sheet.AddMergedRegion(cra);

            //Ngày tháng
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Lâm Thao, Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm " + DateTime.Now.Year.ToString());
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 10);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            
            string[] header_nvkd = { "Ngày xuất", "Tên khách hàng", "Mác HĐ", "Mác TX", "KL bán", "KL thực xuất", "KL NVKD", "Loại đá", "Loại ca bơm","NVKD", "Hình thức ký HĐ" };
            

            cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header_nvkd.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header_nvkd[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }

            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@Index", dlNhanVien.SelectedIndex > 0 ? 1 : 2);
            p[3].SqlDbType = SqlDbType.Int;

            data = ex.GetData("sp_BienBanChotKL_NVKD_Print", p);
            for (int i = 0; i < data.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j < data.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 5 || j == 6 || j == 7)
                    {
                        if (!data.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(data.Rows[i][j].ToString()));
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(data.Rows[i][j].ToString());
                            cell.CellStyle = headerLabelCellStyle;
                        }
                    }
                    else
                    {
                        cell.SetCellValue(data.Rows[i][j].ToString());
                        if (j == 3 || j == 4)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }
            #region Footer
            //Footer
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(2);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            string[] footer = { "F", "G", "H" };
            for (int k = 5; k <= 7; k++)
            {
                cell = row.CreateCell(k);
                cell.CellFormula = "SUM(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                cell.Row.Height = 500;
                cell.CellStyle = hs4;
            }

            //fix border
            for (int i1 = 0; i1 < data.Columns.Count; i1++)
            {
                if (i1 != 2 && i1 != 5 && i1 != 6 && i1 != 7)
                {
                    r1c1 = row.CreateCell(i1);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }
            //End footer
            for (int ik = 0; ik <= data.Columns.Count; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }
            #endregion

            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(1);
            cell.SetCellValue("Nhân viên kinh doanh");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            //truong bp
            cell = row.CreateCell(4);
            cell.SetCellValue("Trưởng phòng hành chính tổng hợp");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            //end NVKD------------------------------------------------------

            //start Xe bơm
            sheet = workbook.CreateSheet("BB chốt KL xe bơm");
            #region Header
            //tao hnag dau tien
            rowIndex = 0;

            //cong ty
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            ngaythang = "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BIÊN BẢN CHỐT KHỐI LƯỢNG XE BƠM " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            
            cra = new CellRangeAddress(2, 2, 0, 6);
            sheet.AddMergedRegion(cra);

            //Ngày tháng
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Lâm Thao, Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm " + DateTime.Now.Year.ToString());
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            
            cra = new CellRangeAddress(3, 3, 0, 6);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            
            string[] header_xebom = { "Ngày xuất", "Tên khách hàng", "Mác HĐ", "Loại đá", "KL thực xuất", "Loại ca bơm" };

            cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header_xebom.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header_xebom[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }

            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
            p[1].SqlDbType = SqlDbType.SmallDateTime;

            data = ex.GetData("sp_BienBanChotKL_XeBom_Print", p);
            for (int i = 0; i < data.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j < data.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 5)
                    {
                        if (!data.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(data.Rows[i][j].ToString()));
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(data.Rows[i][j].ToString());
                            cell.CellStyle = headerLabelCellStyle;
                        }
                    }
                    else
                    {
                        cell.SetCellValue(data.Rows[i][j].ToString());
                        if (j == 3 || j == 4)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }
            #region Footer
            //Footer
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(2);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;
            
            cell = row.CreateCell(5);
            if (data.Rows.Count == 0)
                cell.SetCellValue(0);
            else
                cell.CellFormula = "SUM(F7:F" + rowIndex.ToString() + ")";
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            //fix border
            for (int i1 = 0; i1 < data.Columns.Count; i1++)
            {
                if (i1 != 2 && i1 != 5)
                {
                    r1c1 = row.CreateCell(i1);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }
            //End footer
            for (int ik = 0; ik <= data.Columns.Count; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }
            #endregion
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(0);
            cell.SetCellValue("Vận hành bơm");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            //truong bp
            cell = row.CreateCell(2);
            cell.SetCellValue("Kế toán trạm trộn");
            cell.Row.Height = 500;
            cell.CellStyle = hs2center;

            cell = row.CreateCell(3);
            cell.SetCellValue("Điều hành xe");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            cell = row.CreateCell(5);
            cell.SetCellValue("Trưởng phòng HCTH");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;


            //end xe bơm

            sheet = workbook.CreateSheet("BB chốt KL xe bê tông");
            #region Header
            //tao hnag dau tien
            rowIndex = 0;

            //cong ty
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            ngaythang = "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BIÊN BẢN " + dlLoaiBaoCao.SelectedItem.Text.ToUpper() + " " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 10);
            sheet.AddMergedRegion(cra);

            //Ngày tháng
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Lâm Thao, Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm " + DateTime.Now.Year.ToString());
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 10);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header_xebetong = { "Ngày xuất", "Biển số", "Lái xe", "Ca làm việc", "KM vận chuyển", "Tên khách hàng", "Mác HĐ", "KL thực xuất" };

            cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header_xebetong.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header_xebetong[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }

            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@Index", dlBienSo.SelectedIndex > 0 ? 1 : 2);
            p[3].SqlDbType = SqlDbType.Int;
        
            data = ex.GetData("sp_BienBanChotKL_XeBeTong_Print", p);

            for (int i = 0; i < data.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j < data.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 5 || j == 8)
                    {
                        if (!data.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(data.Rows[i][j].ToString()));
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(data.Rows[i][j].ToString());
                            cell.CellStyle = headerLabelCellStyle;
                        }
                    }
                    else
                    {
                        cell.SetCellValue(data.Rows[i][j].ToString());
                        if (j != 3 && j != 6)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }

                    cell.Row.Height = 500;
                }

            }
            #region Footer
            //Footer
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(3);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            cell = row.CreateCell(8);
            cell.CellFormula = "SUM(I7:I" + rowIndex.ToString() + ")";
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            //fix border
            for (int i1 = 0; i1 < data.Columns.Count; i1++)
            {
                if (i1 != 3 && i1 != 8)
                {
                    r1c1 = row.CreateCell(i1);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }
            //End footer
            for (int ik = 0; ik <= data.Columns.Count; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }
            #endregion
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(0);
            cell.SetCellValue("Lái xe kí nhận");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            //truong bp
            cell = row.CreateCell(3);
            cell.SetCellValue("Trưởng BP điều hành xe");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            cell = row.CreateCell(6);
            cell.SetCellValue("Kế toán trạm trộn");
            cell.Row.Height = 500;
            cell.CellStyle = NoneStyle;

            

            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                
                string saveAsFileName = "BBCKL-"+ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
    }
    void LoadBienSo(DropDownList dlBienSo)
    {
        dlBienSo.Items.Clear();
        dlBienSo.Items.Insert(0, new ListItem("--Chọn biển số--", ""));
        dlBienSo.SelectedIndex = 0;
    }
    void LoadKH(DropDownList dlKhachHang)
    {
        dlKhachHang.Items.Clear();
        dlKhachHang.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        dlKhachHang.SelectedIndex = 0;
    }
    void LoadMacTX(DropDownList dlMac)
    {
        dlMac.Items.Clear();
        dlMac.Items.Insert(0, new ListItem("--Chọn mác thực xuất--", ""));
        dlMac.SelectedIndex = 0;
    }
    void LoadloaiDa(DropDownList dlloaida)
    {
        dlloaida.Items.Clear();
        dlloaida.Items.Insert(0, new ListItem("--Chọn loại đá--", "0"));
        dlloaida.SelectedIndex = 0;
    }
    void LoadBSXByTime(DropDownList dlBienSo)
    {
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlBienSo.Items.Clear();
        dlBienSo.DataSource = ex.GetData("sp_BCNH_BienSoXe_LoadBienSoTime",p);
        dlBienSo.DataBind();
        dlBienSo.Items.Insert(0, new ListItem("--Chọn biển số--", ""));
        dlBienSo.SelectedIndex = 0;
    }
    void LoadBienSoXHByTime(DropDownList dlBienSo)
    {
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlBienSo.Items.Clear();
        dlBienSo.DataSource = ex.GetData("sp_BCDT_BSX_LoadBienSoXe", p);
        dlBienSo.DataBind();
        dlBienSo.Items.Insert(0, new ListItem("--Chọn biển số--", ""));
        dlBienSo.SelectedIndex = 0;
    }
    void LoadNVKDByTime(DropDownList dlNhanVien)
    {
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlNhanVien.Items.Clear();
        dlNhanVien.DataSource = ex.GetData("sp_BienBanChotKL_NVKD_LoadNVKD", p);
        dlNhanVien.DataBind();
        dlNhanVien.Items.Insert(0, new ListItem("--Chọn nhân viên--", ""));
        dlNhanVien.SelectedIndex = 0;
    }
    void LoadNVKD(DropDownList dlNhanVien)
    {
        dlNhanVien.Items.Clear();
        dlNhanVien.Items.Insert(0, new ListItem("--Chọn NVKD--", ""));
        dlNhanVien.SelectedIndex = 0;
    }
    void PrintNPOI(System.Data.DataTable dt,int loai,int all)
    {
        if (all == 2)
        {
            var workbook = new HSSFWorkbook();
            string ngaythang = "";
            if (dt.Rows.Count > 0)
            {
                string sheetname = "Bảng tổng hợp";
                if (dlLoaiBaoCao.SelectedValue.Equals("1"))
                    sheetname = "BB chốt KL Xe ben";
                else
                    if (dlLoaiBaoCao.SelectedValue.Equals("2"))
                        sheetname = "BB chốt KL Trạm Trộn + TN";
                    else
                        if (dlLoaiBaoCao.SelectedValue.Equals("3"))
                            sheetname = "BB chốt KL NVKD";
                        else
                            if (dlLoaiBaoCao.SelectedValue.Equals("4"))
                                sheetname = "BB chốt KL Xe Bơm";
                            else
                                if (dlLoaiBaoCao.SelectedValue.Equals("5"))
                                    sheetname = "BB chốt KL Xe Bê tông";

                var sheet = workbook.CreateSheet(sheetname);

                #region CSS
                var headerLabelCellStyle = workbook.CreateCellStyle();
                headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
                headerLabelCellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                headerLabelCellStyle.WrapText = true;
                headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
                headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
                headerLabelCellStyle.BorderRight = CellBorderType.THIN;
                headerLabelCellStyle.BorderTop = CellBorderType.THIN;

                var headerLabelFont = workbook.CreateFont();
                headerLabelFont.FontName = "Times New Roman";

                headerLabelFont.FontHeightInPoints = 11;
                headerLabelFont.Color = HSSFColor.BLACK.index;
                headerLabelCellStyle.SetFont(headerLabelFont);


                //--------------------------
                var NoneStyle = workbook.CreateCellStyle();

                NoneStyle.SetFont(headerLabelFont);
                ////////////
                var hs2center = workbook.CreateCellStyle();
                hs2center.Alignment = HorizontalAlignment.CENTER;

                var hsb21 = workbook.CreateFont();
                hsb21.FontName = "Times New Roman";

                hsb21.FontHeightInPoints = 11;
                hsb21.Color = HSSFColor.BLACK.index;
                hs2center.SetFont(hsb21);

                /////////////////////////

                //--------------------------

                var hsRight = workbook.CreateCellStyle();
                hsRight.Alignment = HorizontalAlignment.RIGHT;
                hsRight.VerticalAlignment = VerticalAlignment.CENTER;
                hsRight.BorderBottom = CellBorderType.THIN;
                hsRight.BorderLeft = CellBorderType.THIN;
                hsRight.BorderRight = CellBorderType.THIN;
                hsRight.BorderTop = CellBorderType.THIN;

                hsRight.SetFont(headerLabelFont);
                //-------------------
                var hsCenter = workbook.CreateCellStyle();
                hsCenter.Alignment = HorizontalAlignment.CENTER;
                hsCenter.VerticalAlignment = VerticalAlignment.CENTER;
                hsCenter.BorderBottom = CellBorderType.THIN;
                hsCenter.BorderLeft = CellBorderType.THIN;
                hsCenter.BorderRight = CellBorderType.THIN;
                hsCenter.BorderTop = CellBorderType.THIN;

                hsCenter.SetFont(headerLabelFont);
                //-----------------------

                var hs1 = workbook.CreateCellStyle();
                hs1.Alignment = HorizontalAlignment.LEFT;

                var hsb = workbook.CreateFont();
                hsb.Boldweight = (short)FontBoldWeight.BOLD;
                hsb.FontName = "Times New Roman";

                hsb.FontHeightInPoints = 11;
                hsb.Color = HSSFColor.BLACK.index;
                hs1.SetFont(hsb);

                //-------------
                var hs2 = workbook.CreateCellStyle();
                hs2.Alignment = HorizontalAlignment.CENTER;

                var hsb2 = workbook.CreateFont();
                hsb2.Boldweight = (short)FontBoldWeight.BOLD;
                hsb2.FontName = "Times New Roman";

                hsb2.FontHeightInPoints = 13;
                hsb2.Color = HSSFColor.BLACK.index;
                hs2.SetFont(hsb2);
                //------------------------------
                var hs3 = workbook.CreateCellStyle();
                hs3.Alignment = HorizontalAlignment.CENTER;
                //hs3.BorderBottom = CellBorderType.THIN;
                var hsb3 = workbook.CreateFont();
                hsb3.Boldweight = (short)FontBoldWeight.BOLD;
                hsb3.FontName = "Times New Roman";

                hsb3.FontHeightInPoints = 13;
                hsb3.Color = HSSFColor.BLACK.index;
                hs3.SetFont(hsb3);

                //----------------------------
                var hs4 = workbook.CreateCellStyle();
                hs4.Alignment = HorizontalAlignment.CENTER;
                hs4.VerticalAlignment = VerticalAlignment.CENTER;
                hs4.WrapText = true;
                hs4.BorderBottom = CellBorderType.THIN;
                hs4.BorderLeft = CellBorderType.THIN;
                hs4.BorderRight = CellBorderType.THIN;
                hs4.BorderTop = CellBorderType.THIN;
                
                var hsb4 = workbook.CreateFont();
                hsb4.Boldweight = (short)FontBoldWeight.BOLD;
                hsb4.FontName = "Times New Roman";

                hsb4.FontHeightInPoints = 11;
                hsb4.Color = HSSFColor.BLACK.index;
                hs4.SetFont(hsb4);

                #endregion

                //Start header-----------------------------------------------------------------------
                #region Header
                //tao hnag dau tien
                var rowIndex = 0;

                //cong ty
                var row = sheet.CreateRow(rowIndex);
                Cell r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
                sheet.AddMergedRegion(cra);

                //chi nhanh
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(1, 1, 0, 4);
                sheet.AddMergedRegion(cra);

                //tieu de bao cao
                ngaythang = "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("BIÊN BẢN " + dlLoaiBaoCao.SelectedItem.Text.ToUpper() + " " + ngaythang);
                r1c1.CellStyle = hs2;
                r1c1.Row.Height = 500;

                if (loai == 1 || loai==4)
                    cra = new CellRangeAddress(2, 2, 0, 6);
                else if (loai == 2)
                    cra = new CellRangeAddress(2, 2, 0, 7);
                else if (loai==5)
                    cra = new CellRangeAddress(2, 2, 0, 10);
                else if (loai == 3)
                    cra = new CellRangeAddress(2, 2, 0, 11);
                sheet.AddMergedRegion(cra);

                //Ngày tháng
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Lâm Thao, Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm " + DateTime.Now.Year.ToString());
                r1c1.CellStyle = hs3;
                r1c1.Row.Height = 500;

                if (loai == 1 || loai==4)
                    cra = new CellRangeAddress(3, 3, 0, 6);
                else if (loai == 2)
                    cra = new CellRangeAddress(3, 3, 0, 7);
                else if (loai==5)
                    cra = new CellRangeAddress(3, 3, 0, 10);
                else if (loai == 3)
                    cra = new CellRangeAddress(3, 3, 0, 11);
                sheet.AddMergedRegion(cra);

                //freeze panes
                sheet.CreateFreezePane(0, 6);
                #endregion

                #region Header1-2

                //header2
                rowIndex++;
                if (dlLoaiBaoCao.SelectedValue.Equals("1") || dlLoaiBaoCao.SelectedValue.Equals("5"))
                {
                    row = sheet.CreateRow(rowIndex);
                    r1c1 = row.CreateCell(0);
                    r1c1.SetCellValue(dlBienSo.SelectedIndex > 0 ? "Biển số: " + dlBienSo.SelectedItem.Text : "");
                    r1c1.CellStyle = hs3;
                    r1c1.Row.Height = 500;

                    if (loai == 1 || loai == 4)
                        cra = new CellRangeAddress(4, 4, 0, 6);
                    else if (loai == 2)
                        cra = new CellRangeAddress(4, 4, 0, 7);
                    else if (loai == 3)
                        cra = new CellRangeAddress(4, 4, 0, 10);
                    else if (loai == 5)
                        cra = new CellRangeAddress(4, 4, 0, 12);
                    sheet.AddMergedRegion(cra);
                }
                else
                    if (dlLoaiBaoCao.SelectedValue.Equals("3"))
                    {
                        row = sheet.CreateRow(rowIndex);
                        r1c1 = row.CreateCell(0);
                        r1c1.SetCellValue(dlNhanVien.SelectedIndex > 0 ? "NVKD: " + dlNhanVien.SelectedItem.Text : "");
                        r1c1.CellStyle = hs3;
                        r1c1.Row.Height = 500;

                        if (loai == 1 || loai == 4)
                            cra = new CellRangeAddress(4, 4, 0, 6);
                        else if (loai == 2)
                            cra = new CellRangeAddress(4, 4, 0, 7);
                        else if (loai == 3)
                            cra = new CellRangeAddress(4, 4, 0, 10);
                        else if (loai == 5)
                            cra = new CellRangeAddress(4, 4, 0, 12);
                        sheet.AddMergedRegion(cra);
                    }
                    else
                        if (dlLoaiBaoCao.SelectedValue.Equals("2"))
                        {
                            string mac = dlMacTX.SelectedIndex > 0 ? "MÁC "+dlMacTX.SelectedItem.Text.ToUpper() : "";
                            string loaida = dlLoaiDa.SelectedIndex > 0 ? " - "+dlLoaiDa.SelectedItem.Text.ToUpper() : "";
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue(mac+loaida);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            if (loai == 1 || loai == 4)
                                cra = new CellRangeAddress(4, 4, 0, 6);
                            else if (loai == 2)
                                cra = new CellRangeAddress(4, 4, 0, 7);
                            else if (loai == 3)
                                cra = new CellRangeAddress(4, 4, 0, 10);
                            else if (loai == 5)
                                cra = new CellRangeAddress(4, 4, 0, 12);
                            sheet.AddMergedRegion(cra);
                        }
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                string[] header_xeben = { "Ngày nhập", "Biển số", "Nhà cung cấp", "Nơi nhận", "Loại vật liệu", "KL mua" };
                string[] header_tramtron = { "Ngày xuất", "Ca", "Tên khách hàng", "Mác TX", "Loại đá", "KL TX", "Loại PG" };
                string[] header_nvkd = { "Ngày xuất", "Tên khách hàng", "Mác HĐ", "KL bán", "KL thực xuất", "KL NVKD", "Loại đá", "Loại ca bơm","Thành tiền hàng","Số tiền thanh toán","Còn lại","NVKD", "Hình thức ký HĐ" };
                string[] header_xebom = { "Ngày xuất", "Tên khách hàng", "Mác HĐ", "Loại đá", "KL thực xuất", "Loại ca bơm" };
                string[] header_xebetong = { "Ngày xuất","Biển số","Lái xe","Ca","Số KM", "Tên khách hàng", "Mác HĐ", "KL TX","Hệ số","Số tiền","Phí rửa xe","Thành tiền"};

                var cell = row.CreateCell(0);
                cell.SetCellValue("STT");
                cell.CellStyle = hs4;
                cell.Row.Height = 400;


                //tao tieu de cot
                if (loai == 1)
                {
                    for (int j = 0; j < header_xeben.Length; j++)
                    {
                        cell = row.CreateCell(j + 1);
                        cell.SetCellValue(header_xeben[j].ToString());
                        cell.CellStyle = hs4;
                        cell.Row.Height = 500;
                    }
                }
                else
                    if (loai == 2)
                    {
                        for (int j = 0; j < header_tramtron.Length; j++)
                        {
                            cell = row.CreateCell(j + 1);
                            cell.SetCellValue(header_tramtron[j].ToString());
                            cell.CellStyle = hs4;
                            cell.Row.Height = 500;
                        }
                    }
                    else
                        if (loai == 3)
                        {
                            for (int j = 0; j < header_nvkd.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header_nvkd[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                        }
                        else
                            if (loai == 4)
                            {
                                for (int j = 0; j < header_xebom.Length; j++)
                                {
                                    cell = row.CreateCell(j + 1);
                                    cell.SetCellValue(header_xebom[j].ToString());
                                    cell.CellStyle = hs4;
                                    cell.Row.Height = 500;
                                }
                            }
                            else
                                if (loai == 5)
                                {
                                    for (int j = 0; j < header_xebetong.Length; j++)
                                    {
                                        cell = row.CreateCell(j + 1);
                                        cell.SetCellValue(header_xebetong[j].ToString());
                                        cell.CellStyle = hs4;
                                        cell.Row.Height = 500;
                                    }
                                }

                #endregion
                //End header-----------------------------------------------------------------------
                //ghi du lieu tung dong
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                    cell = row.CreateCell(0);
                    cell.SetCellValue((i + 1).ToString());
                    cell.CellStyle = hsCenter;

                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);

                        #region XeBen
                        if (loai == 1)
                        {
                            if (j == 6)
                            {
                                if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                                {
                                    cell.SetCellType(CellType.NUMERIC);
                                    cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                                    cell.CellStyle = hsRight;
                                }
                                else
                                {
                                    cell.SetCellValue(dt.Rows[i][j].ToString());
                                    cell.CellStyle = headerLabelCellStyle;
                                }
                            }
                            else
                            {
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                                cell.CellStyle = headerLabelCellStyle;
                            }
                        }
                        #endregion
                        else
                            #region XeBom
                            if (loai == 4)
                            {
                                if (j == 5)
                                {
                                    if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                                    {
                                        cell.SetCellType(CellType.NUMERIC);
                                        cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                                        cell.CellStyle = hsRight;
                                    }
                                    else
                                    {
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                        cell.CellStyle = headerLabelCellStyle;
                                    }
                                }
                                else
                                {
                                    cell.SetCellValue(dt.Rows[i][j].ToString());
                                    if (j == 3 || j == 4)
                                        cell.CellStyle = hsCenter;
                                    else
                                        cell.CellStyle = headerLabelCellStyle;
                                }
                            }
                            #endregion
                            else
                                #region TramTron
                                if (loai == 2)
                                {
                                    if (j == 6)
                                    {
                                        if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                                        {
                                            cell.SetCellType(CellType.NUMERIC);
                                            cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                                            cell.CellStyle = hsRight;
                                        }
                                        else
                                        {
                                            cell.SetCellValue(dt.Rows[i][j].ToString());
                                            cell.CellStyle = headerLabelCellStyle;
                                        }
                                    }
                                    else
                                    {
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                        if (j == 4 || j==5)
                                            cell.CellStyle = hsCenter;
                                        else
                                            cell.CellStyle = headerLabelCellStyle;
                                    }
                                    
                                }
                                #endregion
                                else
                                #region NVKD
                                if (loai == 3)
                                {
                                    if (j == 5 || j == 6 || j == 4 || j==9)
                                    {
                                        if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                                        {
                                            cell.SetCellType(CellType.NUMERIC);
                                            cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                                            cell.CellStyle = hsRight;
                                        }
                                        else
                                        {
                                            cell.SetCellValue(dt.Rows[i][j].ToString());
                                            cell.CellStyle = headerLabelCellStyle;
                                        }
                                    }
                                    else
                                    {
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                        if (j == 3)
                                            cell.CellStyle = hsCenter;
                                        else
                                            cell.CellStyle = headerLabelCellStyle;
                                    }

                                }
                                #endregion
                                else
                                    #region XeBeTong
                                    if (loai == 5)
                                    {
                                        if (j == 5 || j >=8)
                                        {
                                            if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                                            {
                                                cell.SetCellType(CellType.NUMERIC);
                                                cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                                                cell.CellStyle = hsRight;
                                            }
                                            else
                                            {
                                                cell.SetCellValue(dt.Rows[i][j].ToString());
                                                cell.CellStyle = headerLabelCellStyle;
                                            }
                                        }
                                        else
                                        {
                                            cell.SetCellValue(dt.Rows[i][j].ToString());
                                            if (j !=3 && j!=6)
                                                cell.CellStyle = hsCenter;
                                            else
                                                cell.CellStyle = headerLabelCellStyle;
                                        }

                                    }
                                    #endregion
                                    else
                                    {
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                        cell.CellStyle = headerLabelCellStyle;
                                    }
                        //if(loai==2)
                            //cell.Row.Height = 1373;
                        //else
                        //cell.Row.Height = 500;
                    }

                }
                #region Footer
                //Footer
                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                if (loai == 1)
                {
                    cell = row.CreateCell(3);
                    cell.SetCellValue("Tổng cộng");
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;

                    cell = row.CreateCell(6);
                    cell.CellFormula = "SUM(G7:G" + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;

                    //fix border
                    for (int i1 = 0; i1 < dt.Columns.Count; i1++)
                    {
                        if (i1 != 3 && i1 != 6)
                        {
                            r1c1 = row.CreateCell(i1);
                            r1c1.CellStyle = hs4;
                            r1c1.Row.Height = 400;
                        }
                    }
                    rowIndex++;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue("Lái xe kí nhận");
                    cell.Row.Height = 500;
                    cell.CellStyle = NoneStyle;

                    //truong bp
                    cell = row.CreateCell(2);
                    cell.SetCellValue("Trưởng BP điều hành xe");
                    cell.Row.Height = 500;
                    cell.CellStyle = NoneStyle;

                    //ke toan
                    cell = row.CreateCell(4);
                    cell.SetCellValue("Kế toán");
                    cell.Row.Height = 500;
                    cell.CellStyle = NoneStyle;

                    //truong hcth
                    cell = row.CreateCell(5);
                    cell.SetCellValue("Trưởng bộ phận HCTH");
                    cell.Row.Height = 500;
                    cell.CellStyle = NoneStyle;

                }
                else
                    if (loai == 2 || loai == 4)
                    {
                        cell = row.CreateCell(3);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        if (loai == 4)
                        {
                            cell = row.CreateCell(5);
                            cell.CellFormula = "SUM(F7:F" + rowIndex.ToString() + ")";
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            //fix border
                            for (int i1 = 0; i1 < dt.Columns.Count; i1++)
                            {
                                if (i1 != 3 && i1 != 5)
                                {
                                    r1c1 = row.CreateCell(i1);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                        }
                        else
                        {
                            cell = row.CreateCell(6);
                            cell.CellFormula = "SUM(G7:G" + rowIndex.ToString() + ")";
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            //fix border
                            for (int i1 = 0; i1 < dt.Columns.Count; i1++)
                            {
                                if (i1 != 3 && i1 != 6)
                                {
                                    r1c1 = row.CreateCell(i1);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                        }
                        if (loai == 2)
                        {

                            rowIndex++;
                            

                            //
                            int index2 = 1;
                            if (dlKhachHang.SelectedIndex.Equals(0) && dlMacTX.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                                index2 = 1;
                            else if (dlKhachHang.SelectedIndex > 0 && dlMacTX.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                                index2 = 2;
                            else if (dlKhachHang.SelectedIndex > 0 && dlMacTX.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
                                index2 = 3;
                            else if (dlKhachHang.SelectedIndex > 0 && dlMacTX.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
                                index2 = 4;

                            p = new SqlParameter[6];
                            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                            p[0].SqlDbType = SqlDbType.SmallDateTime;
                            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                            p[1].SqlDbType = SqlDbType.SmallDateTime;

                            p[2] = new SqlParameter("@Index", index2);
                            p[2].SqlDbType = SqlDbType.Int;
                            p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                            p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                            p[4] = new SqlParameter("@MacTX", ex.GetGuid(dlMacTX.SelectedValue));
                            p[4].SqlDbType = SqlDbType.UniqueIdentifier;
                            p[5] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
                            p[5].SqlDbType = SqlDbType.Int;

                            dt = ex.GetData("sp_BienBanChotKL_TramTron_TongKL_Print", p);
                            if (dt.Rows.Count > 0)
                            {
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(0);
                                cell.SetCellValue("Trong đó");
                                cell.CellStyle = hs3;
                                cra = new CellRangeAddress(rowIndex, rowIndex, 0, 1);
                                sheet.AddMergedRegion(cra);
                                for(int i=0;i<dt.Rows.Count;i++)
                                {
                                    rowIndex++;
                                    row = sheet.CreateRow(rowIndex);
                                    cell = row.CreateCell(0);
                                    cell.SetCellValue(dt.Rows[i]["KhachHang"].ToString());
                                    cell.CellStyle = NoneStyle;
                                    cra = new CellRangeAddress(rowIndex, rowIndex, 0, 3);
                                    sheet.AddMergedRegion(cra);

                                    cell = row.CreateCell(4);
                                    cell.SetCellValue(dt.Rows[i]["MacThucXuat"].ToString());
                                    cell.CellStyle = NoneStyle;
                                    cell = row.CreateCell(5);
                                    cell.SetCellValue(dt.Rows[i]["LoaiDa"].ToString());
                                    cell.CellStyle = NoneStyle;
                                    cell = row.CreateCell(6);
                                    cell.SetCellValue(dt.Rows[i]["KLThucXuat"].ToString());
                                    cell.CellStyle = NoneStyle;
                                }
                            }
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex+2);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("Bộ phận thí nghiệm                          Bộ phận vận hành trạm                            Kế toán trạm trộn");
                            cell.CellStyle = NoneStyle;
                        }
                        else
                            if (loai == 4)
                            {
                                rowIndex++;
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(0);
                                cell.SetCellValue("Vận hành bơm");
                                cell.Row.Height = 500;
                                cell.CellStyle = NoneStyle;

                                //truong bp
                                cell = row.CreateCell(2);
                                cell.SetCellValue("Kế toán trạm trộn");
                                cell.Row.Height = 500;
                                cell.CellStyle = hs2center;

                                cell = row.CreateCell(3);
                                cell.SetCellValue("Điều hành xe");
                                cell.Row.Height = 500;
                                cell.CellStyle = NoneStyle;

                                cell = row.CreateCell(5);
                                cell.SetCellValue("Trưởng phòng HCTH");
                                cell.Row.Height = 500;
                                cell.CellStyle = NoneStyle;
                            }
                    }
                    else
                        if (loai == 3)
                        {
                            cell = row.CreateCell(2);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            string[] footer = { "E","F", "G" };
                            for (int k = 4; k <= 6; k++)
                            {
                                cell = row.CreateCell(k);
                                cell.CellFormula = "SUM(" + footer[k - 4] + "7:" + footer[k - 4] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                            cell = row.CreateCell(9);
                            cell.CellFormula = "SUM(J7:J" + rowIndex.ToString() + ")";
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            //fix border
                            for (int i1 = 0; i1 < dt.Columns.Count; i1++)
                            {
                                if (i1 != 2 && i1 != 4 && i1 != 5 && i1 != 6 && i1!=9)
                                {
                                    r1c1 = row.CreateCell(i1);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(1);
                            cell.SetCellValue("Nhân viên kinh doanh");
                            cell.Row.Height = 500;
                            cell.CellStyle = NoneStyle;

                            //truong bp
                            cell = row.CreateCell(4);
                            cell.SetCellValue("Trưởng phòng hành chính tổng hợp");
                            cell.Row.Height = 500;
                            cell.CellStyle = NoneStyle;
                        }
                        else
                            if (loai == 5)
                            {
                                cell = row.CreateCell(3);
                                cell.SetCellValue("Tổng cộng");
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;


                                cell = row.CreateCell(8);
                                cell.CellFormula = "SUM(I7:I" + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;

                                cell = row.CreateCell(11);
                                cell.CellFormula = "SUM(L7:L" + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;

                                cell = row.CreateCell(12);
                                cell.CellFormula = "SUM(M7:M" + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;

                                //fix border
                                for (int i1 = 0; i1 < dt.Columns.Count; i1++)
                                {
                                    if (i1 != 3 && i1 != 8 && i1!=11 && i1!=12)
                                    {
                                        r1c1 = row.CreateCell(i1);
                                        r1c1.CellStyle = hs4;
                                        r1c1.Row.Height = 400;
                                    }
                                }
                                rowIndex++;
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(0);
                                cell.SetCellValue("Lái xe kí nhận");
                                cell.Row.Height = 500;
                                cell.CellStyle = NoneStyle;

                                //truong bp
                                cell = row.CreateCell(3);
                                cell.SetCellValue("Trưởng BP điều hành xe");
                                cell.Row.Height = 500;
                                cell.CellStyle = NoneStyle;

                                cell = row.CreateCell(6);
                                cell.SetCellValue("Kế toán trạm trộn");
                                cell.Row.Height = 500;
                                cell.CellStyle = NoneStyle;

                            }
                //End footer
                if (loai != 2 && loai !=5 && loai!=3 && loai!=4)
                {
                    for (int ik = 0; ik <= dt.Columns.Count; ik++)
                    {
                        sheet.AutoSizeColumn(ik);
                    }
                }
                else
                {
                    if (loai == 2)
                    {
                        int[] width = { 40, 72, 35, 187, 60, 53, 55, 100 };
                        for (int ik = 0; ik <= 7; ik++)
                        {
                            sheet.SetColumnWidth(ik, width[ik] * 36);

                        }
                    }
                    else if (loai == 5)
                    {
                        int[] width5 = { 30, 72, 75, 121, 35, 54, 193, 63, 53, 46, 52, 74, 77 };
                        for (int ik = 0; ik <= 12; ik++)
                        {
                            sheet.SetColumnWidth(ik, width5[ik] * 36);

                        }
                    }
                    else if (loai == 3)
                    {
                        int[] width3 = { 35,72,147,49,44,53,50,55,83,66,86,52,72,78 };
                        for (int ik = 0; ik <= 13; ik++)
                        {
                            sheet.SetColumnWidth(ik, width3[ik] * 36);

                        }
                    }
                    else if (loai == 4)
                    {
                        int[] width4 = { 44,77,293,83,55,103,86 };
                        for (int ik = 0; ik <= 6; ik++)
                        {
                            sheet.SetColumnWidth(ik, width4[ik] * 36);
                        }
                    }
                }
                #endregion

                //end sheet tong hop--------------------------
                using (var exportData = new MemoryStream())
                {
                    workbook.Write(exportData);
                    string filename = "";
                    if (dlLoaiBaoCao.SelectedValue.Equals("1"))
                        filename = "XeBen";
                    else
                        if (dlLoaiBaoCao.SelectedValue.Equals("2"))
                            filename = "TramTron + TN";
                        else
                            if (dlLoaiBaoCao.SelectedValue.Equals("3"))
                                filename = "NVKD";
                            else
                                if (dlLoaiBaoCao.SelectedValue.Equals("4"))
                                    filename = "XeBom";
                                else
                                    if (dlLoaiBaoCao.SelectedValue.Equals("5"))
                                        filename = "XeBeTong";

                    string saveAsFileName = "BBCKL-" + filename + " " + ngaythang + ".xls";

                    Response.ContentType = "application/vnd.ms-excel";
                    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                    Response.Clear();
                    Response.BinaryWrite(exportData.GetBuffer());
                    Response.End();
                }
            }
            else gstGetMess("Không có bản ghi nào để in", "");
        }
        else
            PrintAll();
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (f_SuDung.Equals(1))
        {
            CurrentPage = 1;
            btnNext.Enabled = true;
            Search(1,int.Parse(dlLoaiBaoCao.SelectedValue));
        }
        else
            gstGetMess("Bạn không có quyền xem báo cáo này", "");
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    void Search(int page, int loai)
    {

        p = new SqlParameter[4];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@PageNumber", page);
        p[2].SqlDbType = SqlDbType.Int;
        p[3] = new SqlParameter("@RowspPage", PageNumber);
        p[3].SqlDbType = SqlDbType.Int;

        if (loai == 1)
        {
            GVXeben.Visible = true;
            GVTramTron.Visible = false;
            GVNVKD.Visible = false;
            GVXeBom.Visible = false;
            gvXeBeTong.Visible = false;

            p = new SqlParameter[6];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@PageNumber", page);
            p[2].SqlDbType = SqlDbType.Int;
            p[3] = new SqlParameter("@RowspPage", PageNumber);
            p[3].SqlDbType = SqlDbType.Int;
            p[4] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
            p[4].SqlDbType = SqlDbType.UniqueIdentifier;
            p[5] = new SqlParameter("@Index", dlBienSo.SelectedIndex>0?1:2);
            p[5].SqlDbType = SqlDbType.Int;

            GVXeben.DataSource = ex.GetData("sp_BienBanChotKL_XeBen", p);
            GVXeben.DataBind();

            if (page == 1)
            {
                p = new SqlParameter[4];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@Index", dlBienSo.SelectedIndex > 0 ? 1 : 2);
                p[3].SqlDbType = SqlDbType.Int;

                var tt = ex.GetData("sp_BienBanChotKL_XeBen_GetFooter", p);
                if (tt.Rows.Count > 0)
                {
                    sochuyen = double.Parse(tt.Rows[0][0].ToString());
                    klxeben_klmua = double.Parse(tt.Rows[0]["KLMua"].ToString());
                }

            }
            if (GVXeben.Rows.Count > 0)
            {
                GVXeben.FooterRow.Cells[3].Text = "Tổng cộng " + sochuyen.ToString() + " chuyến";
                GVXeben.FooterRow.Cells[6].Text = ex.ConvertDecimal(klxeben_klmua);
            }
        }
        else
            if (loai == 2)
            {
                GVXeben.Visible = false;
                GVTramTron.Visible = true;
                GVNVKD.Visible = false;
                GVXeBom.Visible = false;
                gvXeBeTong.Visible = false;

                int index = 1;
                if (dlKhachHang.SelectedIndex.Equals(0) && dlMacTX.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 1;
                else if (dlKhachHang.SelectedIndex>0 && dlMacTX.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 2;
                else if (dlKhachHang.SelectedIndex>0 && dlMacTX.SelectedIndex>0 && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 3;
                else if (dlKhachHang.SelectedIndex>0 && dlMacTX.SelectedIndex>0 && dlLoaiDa.SelectedIndex>0)
                    index = 4;

                p = new SqlParameter[8];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@PageNumber", page);
                p[2].SqlDbType = SqlDbType.Int;
                p[3] = new SqlParameter("@RowspPage", PageNumber);
                p[3].SqlDbType = SqlDbType.Int;

                p[4] = new SqlParameter("@Index", index);
                p[4].SqlDbType = SqlDbType.Int;
                p[5] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                p[6] = new SqlParameter("@MacTX", ex.GetGuid(dlMacTX.SelectedValue));
                p[6].SqlDbType = SqlDbType.UniqueIdentifier;
                p[7] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
                p[7].SqlDbType = SqlDbType.Int;

                GVTramTron.DataSource = ex.GetData("sp_BienBanChotKL_TramTron", p);
                GVTramTron.DataBind();

                if (page == 1)
                {
                    p = new SqlParameter[6];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    

                    p[2] = new SqlParameter("@Index", index);
                    p[2].SqlDbType = SqlDbType.Int;
                    p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                    p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[4] = new SqlParameter("@MacTX", ex.GetGuid(dlMacTX.SelectedValue));
                    p[4].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[5] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
                    p[5].SqlDbType = SqlDbType.Int;

                    var tt = ex.GetData("sp_BienBanChotKL_TramTron_GetFooter", p);
                    if (tt.Rows.Count > 0)
                    {
                        sochuyen = double.Parse(tt.Rows[0][0].ToString());
                        kltram_kltx =  double.Parse(tt.Rows[0][1].ToString());
                    }

                }
                if (GVTramTron.Rows.Count > 0)
                {
                    GVTramTron.FooterRow.Cells[2].Text = "Tổng cộng "+sochuyen.ToString()+" chuyến";
                    GVTramTron.FooterRow.Cells[5].Text = ex.ConvertDecimal(kltram_kltx);
                }
            }
            else
                if (loai == 3)
                {
                    GVXeben.Visible = false;
                    GVTramTron.Visible = false;
                    GVNVKD.Visible = true;
                    GVXeBom.Visible = false;
                    gvXeBeTong.Visible = false;

                    p = new SqlParameter[6];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@PageNumber", page);
                    p[2].SqlDbType = SqlDbType.Int;
                    p[3] = new SqlParameter("@RowspPage", PageNumber);
                    p[3].SqlDbType = SqlDbType.Int;
                    p[4] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
                    p[4].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[5] = new SqlParameter("@Index", dlNhanVien.SelectedIndex > 0 ? 1 : 2);
                    p[5].SqlDbType = SqlDbType.Int;

                    GVNVKD.DataSource = ex.GetData("sp_BienBanChotKL_NVKD", p);
                    GVNVKD.DataBind();

                    if (page == 1)
                    {
                        p = new SqlParameter[4];
                        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                        p[0].SqlDbType = SqlDbType.SmallDateTime;
                        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                        p[1].SqlDbType = SqlDbType.SmallDateTime;
                        p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
                        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[3] = new SqlParameter("@Index", dlNhanVien.SelectedIndex > 0 ? 1 : 2);
                        p[3].SqlDbType = SqlDbType.Int;

                        var tt = ex.GetData("sp_BienBanChotKL_NVKD_GetFooter", p);
                        if (tt.Rows.Count > 0)
                        {
                            sochuyen = double.Parse(tt.Rows[0][0].ToString());
                            klnvkd_klban = double.Parse(tt.Rows[0]["KLKhachHang"].ToString());
                            klnvkd_kltx = double.Parse(tt.Rows[0]["KLThucXuat"].ToString());
                            klnvkd_klnvkd = double.Parse(tt.Rows[0]["KLNVKD"].ToString());
                            klnvkd_ttban = double.Parse(tt.Rows[0]["ThanhTienHang"].ToString());
                        }

                    }
                    if (GVNVKD.Rows.Count > 0)
                    {
                        GVNVKD.FooterRow.Cells[2].Text = "Tổng cộng";// +sochuyen.ToString() + " chuyến";
                        GVNVKD.FooterRow.Cells[4].Text = ex.ConvertDecimal(klnvkd_klban);
                        GVNVKD.FooterRow.Cells[5].Text = ex.ConvertDecimal(klnvkd_kltx);
                        GVNVKD.FooterRow.Cells[6].Text = ex.ConvertDecimal(klnvkd_klnvkd);
                        GVNVKD.FooterRow.Cells[9].Text = ex.ConvertDecimal(klnvkd_ttban);
                        
                    }
                }
                else
                    if (loai == 4)
                    {
                        GVXeben.Visible = false;
                        GVTramTron.Visible = false;
                        GVNVKD.Visible = false;
                        GVXeBom.Visible = true;
                        gvXeBeTong.Visible = false;

                        GVXeBom.DataSource = ex.GetData("sp_BienBanChotKL_XeBom", p);
                        GVXeBom.DataBind();

                        if (page == 1)
                        {
                            p = new SqlParameter[2];
                            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                            p[0].SqlDbType = SqlDbType.SmallDateTime;
                            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                            p[1].SqlDbType = SqlDbType.SmallDateTime;

                            var tt = ex.GetData("sp_BienBanChotKL_XeBom_GetFooter", p);
                            if (tt.Rows.Count > 0)
                            {
                                sochuyen = double.Parse(tt.Rows[0][0].ToString());
                                klxebom_kltx = double.Parse(tt.Rows[0]["KLThucXuat"].ToString());
                            }

                        }
                        if (GVXeBom.Rows.Count > 0)
                        {
                            GVXeBom.FooterRow.Cells[2].Text = "Tổng cộng";// +sochuyen.ToString() + " chuyến";
                            GVXeBom.FooterRow.Cells[5].Text = ex.ConvertDecimal(klxebom_kltx);
                        }
                    }
                    else
                        if (loai == 5)
                        {
                            GVXeben.Visible = false;
                            GVTramTron.Visible = false;
                            GVNVKD.Visible = false;
                            GVXeBom.Visible = false;
                            gvXeBeTong.Visible = true;

                            p = new SqlParameter[6];
                            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                            p[0].SqlDbType = SqlDbType.SmallDateTime;
                            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                            p[1].SqlDbType = SqlDbType.SmallDateTime;
                            p[2] = new SqlParameter("@PageNumber", page);
                            p[2].SqlDbType = SqlDbType.Int;
                            p[3] = new SqlParameter("@RowspPage", PageNumber);
                            p[3].SqlDbType = SqlDbType.Int;
                            p[4] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
                            p[4].SqlDbType = SqlDbType.UniqueIdentifier;
                            p[5] = new SqlParameter("@Index", dlBienSo.SelectedIndex > 0 ? 1 : 2);
                            p[5].SqlDbType = SqlDbType.Int;

                            gvXeBeTong.DataSource = ex.GetData("sp_BienBanChotKL_XeBeTong", p);
                            gvXeBeTong.DataBind();

                            if (page == 1)
                            {
                                p = new SqlParameter[4];
                                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                                p[0].SqlDbType = SqlDbType.SmallDateTime;
                                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                                p[1].SqlDbType = SqlDbType.SmallDateTime;
                                p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
                                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                                p[3] = new SqlParameter("@Index", dlBienSo.SelectedIndex > 0 ? 1 : 2);
                                p[3].SqlDbType = SqlDbType.Int;

                                var tt = ex.GetData("sp_BienBanChotKL_XeBeTong_GetFooter", p);
                                if (tt.Rows.Count > 0)
                                {
                                    sochuyen = double.Parse(tt.Rows[0][0].ToString());
                                    klxebt_kltx = double.Parse(tt.Rows[0]["KLThucXuat"].ToString());
                                    phiruaxe = double.Parse(tt.Rows[0]["PhiRuaXe"].ToString());
                                    thanhtien_xebom = double.Parse(tt.Rows[0]["ThanhTien"].ToString());
                                }

                            }
                            if (gvXeBeTong.Rows.Count > 0)
                            {
                                gvXeBeTong.FooterRow.Cells[3].Text = "Tổng cộng " + sochuyen.ToString() + " chuyến";
                                gvXeBeTong.FooterRow.Cells[8].Text = ex.ConvertDecimal(klxebt_kltx);
                                gvXeBeTong.FooterRow.Cells[11].Text = ex.ConvertDecimal(phiruaxe);
                                gvXeBeTong.FooterRow.Cells[12].Text = ex.ConvertDecimal(thanhtien_xebom);
                            }
                        }
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            try
            {
                int index = int.Parse(dlLoaiBaoCao.SelectedValue);

                p = new SqlParameter[2];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                p[1].SqlDbType = SqlDbType.SmallDateTime;

                if (index == 1)
                {
                    p = new SqlParameter[4];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
                    p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[3] = new SqlParameter("@Index", dlBienSo.SelectedIndex > 0 ? 1 : 2);
                    p[3].SqlDbType = SqlDbType.Int;

                    dtPrint = ex.GetData("sp_BienBanChotKL_XeBen_Print", p);
                }
                else if (index == 2)
                {
                    int index2 = 1;
                    if (dlKhachHang.SelectedIndex.Equals(0) && dlMacTX.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                        index2 = 1;
                    else if (dlKhachHang.SelectedIndex > 0 && dlMacTX.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                        index2 = 2;
                    else if (dlKhachHang.SelectedIndex > 0 && dlMacTX.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
                        index2 = 3;
                    else if (dlKhachHang.SelectedIndex > 0 && dlMacTX.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
                        index2 = 4;

                    p = new SqlParameter[6];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;

                    p[2] = new SqlParameter("@Index", index2);
                    p[2].SqlDbType = SqlDbType.Int;
                    p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                    p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[4] = new SqlParameter("@MacTX", ex.GetGuid(dlMacTX.SelectedValue));
                    p[4].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[5] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
                    p[5].SqlDbType = SqlDbType.Int;

                    dtPrint = ex.GetData("sp_BienBanChotKL_TramTron_Print", p);
                }
                else if (index == 3)
                {
                    p = new SqlParameter[4];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
                    p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[3] = new SqlParameter("@Index", dlNhanVien.SelectedIndex > 0 ? 1 : 2);
                    p[3].SqlDbType = SqlDbType.Int;

                    dtPrint = ex.GetData("sp_BienBanChotKL_NVKD_Print", p);
                }
                else if (index == 4)
                    dtPrint = ex.GetData("sp_BienBanChotKL_XeBom_Print", p);
                else if (index == 5)
                {
                    p = new SqlParameter[4];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
                    p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[3] = new SqlParameter("@Index", dlBienSo.SelectedIndex > 0 ? 1 : 2);
                    p[3].SqlDbType = SqlDbType.Int;
                    dtPrint = ex.GetData("sp_BienBanChotKL_XeBeTong_Print", p);
                }

                PrintNPOI(dtPrint, index, ckAll.Checked ? 1 : 2);
            }
            catch (Exception ax)
            {
                gstGetMess(ax.Message, "");

            }
        }
        else
            gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)
        {
            e.Row.Attributes.Add("onmouseover", "this.originalstyle=this.style.backgroundColor;this.style.backgroundColor='#EEFFAA'");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=this.originalstyle;");
        }
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage,int.Parse(dlLoaiBaoCao.SelectedValue));

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage, int.Parse(dlLoaiBaoCao.SelectedValue));
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage, int.Parse(dlLoaiBaoCao.SelectedValue));

       
        btnPre.Enabled = true;
        btnNext.Enabled = true;
        btnFirst.Enabled = true;
    }

    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        ckAll.Checked = false;
        dlNhanVien.Visible = dlLoaiBaoCao.SelectedValue.Equals("3");
        dlBienSo.Visible = dlLoaiBaoCao.SelectedValue.Equals("1") || dlLoaiBaoCao.SelectedValue.Equals("5");
        
        dlKhachHang.Visible = dlLoaiBaoCao.SelectedValue.Equals("2");
        dlMacTX.Visible = dlLoaiBaoCao.SelectedValue.Equals("2");
        dlLoaiDa.Visible = dlLoaiBaoCao.SelectedValue.Equals("2");

        if (dlLoaiBaoCao.SelectedValue.Equals("1"))
            LoadBSXByTime(dlBienSo);
        else if (dlLoaiBaoCao.SelectedValue.Equals("5"))
            LoadBienSoXHByTime(dlBienSo);
        else if (dlLoaiBaoCao.SelectedValue.Equals("3"))
            LoadNVKDByTime(dlNhanVien);
        else if (dlLoaiBaoCao.SelectedValue.Equals("2"))
        {
            dlKhachHang.Visible = true;
            dlMacTX.Visible = true;
            dlLoaiDa.Visible = true;

            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
            p[1].SqlDbType = SqlDbType.SmallDateTime;

            dlKhachHang.Items.Clear();
            dlKhachHang.DataSource = ex.GetData("sp_BCXH_LoadKhachHangByTime", p);
            dlKhachHang.DataBind();
            dlKhachHang.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
            dlKhachHang.SelectedIndex = 0;

            LoadMacTX(dlMacTX);
            LoadloaiDa(dlLoaiDa);

        }
            
    }


    protected void dlKhachHang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlKhachHang.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlMacTX.Items.Clear();
            dlMacTX.DataSource = ex.GetData("sp_BCXH_LoadMacTXByKhachHang", p);
            dlMacTX.DataBind();
            dlMacTX.Items.Insert(0, new ListItem("--Chọn mác thực xuất--", ""));
            dlMacTX.SelectedIndex = 0;
        }
        LoadloaiDa(dlLoaiDa);
    }
    protected void dlMacTX_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlMacTX.SelectedIndex > 0)
        {
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@MacTX", ex.GetGuid(dlMacTX.SelectedValue));
            p[3].SqlDbType = SqlDbType.UniqueIdentifier;

            dlLoaiDa.Items.Clear();
            dlLoaiDa.DataSource = ex.GetData("sp_BCXH_LoadLoaiDaByMacTX", p);
            dlLoaiDa.DataBind();
            dlLoaiDa.Items.Insert(0, new ListItem("--Chọn loại đá--", "0"));
            dlLoaiDa.SelectedIndex = 0;
        }
        else
            LoadloaiDa(dlLoaiDa);
    }
}